const mysql = require('mysql2/promise');
const config = require('../config');
const bcrypt = require('bcryptjs');
const crypto = require('crypto');

async function initializeDatabase() {
  const dbConfig = {
    host: process.env.DB_HOST || config.db.host,
    user: process.env.DB_USER || config.db.user,
    password: process.env.DB_PASSWORD || config.db.password,
    database: process.env.DB_NAME || config.db.database
  };

  const connection = await mysql.createConnection({
    host: dbConfig.host,
    user: dbConfig.user,
    password: dbConfig.password
  });

  try {
    const dbName = dbConfig.database;
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${dbName}`);
    console.log(`Database ${dbName} created or already exists.`);
    await connection.query(`USE ${dbName}`);

    // === 1. 删除所有表（按依赖顺序）===
    // 禁用外键检查
    await connection.query(`SET FOREIGN_KEY_CHECKS = 0`);

    // 删除所有表（从最依赖的表开始）
    await connection.query(`DROP TABLE IF EXISTS experiment_scores`);
    await connection.query(`DROP TABLE IF EXISTS experiment_criteria`);
    await connection.query(`DROP TABLE IF EXISTS experiment_classes`);
    await connection.query(`DROP TABLE IF EXISTS experiment_objectives`);
    await connection.query(`DROP TABLE IF EXISTS experiment_resources`);
    await connection.query(`DROP TABLE IF EXISTS experiments`);
    await connection.query(`DROP TABLE IF EXISTS class_students`);
    await connection.query(`DROP TABLE IF EXISTS classes`);
    await connection.query(`DROP TABLE IF EXISTS courses`);
    await connection.query(`DROP TABLE IF EXISTS students`);
    await connection.query(`DROP TABLE IF EXISTS teachers`);

    // 重新启用外键检查
    await connection.query(`SET FOREIGN_KEY_CHECKS = 1`);

    // === 2. 创建所有表（按依赖顺序）===
    // 2.1 创建基础表
    await connection.query(`
      CREATE TABLE teachers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        employee_no VARCHAR(20) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE,
        password VARCHAR(255) NOT NULL,
        faculty VARCHAR(100),
        department VARCHAR(100),
        phone VARCHAR(20),
        is_admin BOOLEAN DEFAULT false
      )
    `);

    await connection.query(`
      CREATE TABLE students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        s_no VARCHAR(20) UNIQUE NOT NULL,
        s_name VARCHAR(50) NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100) UNIQUE,
        class VARCHAR(50) NOT NULL,
        enrollment_year YEAR
      )
    `);

    await connection.query(`
      CREATE TABLE courses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        course_code VARCHAR(20) UNIQUE NOT NULL,
        course_name VARCHAR(255) NOT NULL,
        credit TINYINT UNSIGNED DEFAULT 4,
        description TEXT,
        faculty VARCHAR(100),
        status ENUM('active', 'inactive') DEFAULT 'active'
      )
    `);

    await connection.query(`
      CREATE TABLE classes (
        id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
        name VARCHAR(255) NOT NULL,
        status ENUM('active', 'archived') DEFAULT 'active',
        semester VARCHAR(20) NOT NULL,
        teacher_id INT NOT NULL,
        course_id INT NOT NULL,
        FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
      )
    `);

    await connection.query(`
      CREATE TABLE class_students (
        class_id VARCHAR(36) NOT NULL,
        student_id INT NOT NULL,
        PRIMARY KEY (class_id, student_id),
        FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
      )
    `);

    // 2.2 创建实验相关表
    await connection.query(`
      CREATE TABLE experiments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        short_description TEXT,
        full_description TEXT NOT NULL,
        course_name VARCHAR(255) NOT NULL,       
        duration SMALLINT UNSIGNED NOT NULL,
        image_url VARCHAR(255),
        publish_date DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);

    await connection.query(`
      CREATE TABLE experiment_objectives (
        id INT AUTO_INCREMENT PRIMARY KEY,
        experiment_id INT NOT NULL,
        objective VARCHAR(255) NOT NULL,
        FOREIGN KEY (experiment_id) REFERENCES experiments(id) ON DELETE CASCADE
      )
    `);

    await connection.query(`
      CREATE TABLE experiment_resources (
        id INT AUTO_INCREMENT PRIMARY KEY,
        experiment_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        type ENUM('pdf','docx','code','video') NOT NULL,
        file_path VARCHAR(255) NOT NULL,
        is_public BOOLEAN DEFAULT false,
        FOREIGN KEY (experiment_id) REFERENCES experiments(id) ON DELETE CASCADE
      )
    `);

    await connection.query(`
      CREATE TABLE experiment_criteria (
        id INT AUTO_INCREMENT PRIMARY KEY,
        experiment_id INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        proportion DECIMAL(5,2) NOT NULL,
        FOREIGN KEY (experiment_id) REFERENCES experiments(id) ON DELETE CASCADE
      )
    `);

    await connection.query(`
      CREATE TABLE experiment_classes (
        experiment_id INT NOT NULL,
        class_id VARCHAR(36) NOT NULL,
        deadline DATETIME,
        PRIMARY KEY (experiment_id, class_id),
        FOREIGN KEY (experiment_id) REFERENCES experiments(id) ON DELETE CASCADE,
        FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
      )
    `);

    await connection.query(`
      CREATE TABLE experiment_scores (
        id INT AUTO_INCREMENT PRIMARY KEY,
        experiment_id INT NOT NULL,
        student_id INT NOT NULL,
        criteria_id INT NOT NULL,
        score DECIMAL(5,2),
        submission_time DATETIME,
        status ENUM('未提交', '已提交', '已批改') DEFAULT '未提交',
        FOREIGN KEY (experiment_id) REFERENCES experiments(id) ON DELETE CASCADE,
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
        FOREIGN KEY (criteria_id) REFERENCES experiment_criteria(id) ON DELETE CASCADE
      )
    `);

    console.log('All tables created successfully.');
    await seedInitialData(connection);

  } catch (error) {
    console.error('Error initializing database:', error);
  } finally {
    await connection.end();
  }
}

async function encryptPassword(password) {
  const sha256Hash = crypto.createHash('sha256')
    .update(password)
    .digest('hex');
  return bcrypt.hash(sha256Hash, 10);
}

async function seedInitialData(connection) {
  try {
    const encryptedPassword = await encryptPassword('password123');

    // 1. 插入教师
    await connection.query(`
      INSERT IGNORE INTO teachers (employee_no, name, email, password, faculty, department, phone, is_admin) 
      VALUES 
        ('202206170001', '张教授', 'zhang@university.edu', ?, '计算机学院', '软件工程系', '13800138001', false),
        ('202406170003', '李副教授', 'li@university.edu', ?, '人工智能学院', '机器学习系', '13900139002', false),
        ('202406170004', '叶老师', 'ye@neu.edu.cn', ?, '计算机学院', '网络工程系', '13700137003', true)
    `, [encryptedPassword, encryptedPassword, encryptedPassword]);

    // 2. 插入课程
    await connection.query(`
      INSERT IGNORE INTO courses (course_code, course_name, credit, faculty) 
      VALUES 
        ('CS3207', '计算机系统基础', 4, '计算机学院'),
        ('AI4101', '机器学习导论', 3, '人工智能学院'),
        ('CS4205', '计算机网络', 4, '计算机学院')
    `);

    // 3. 插入学生
    await connection.query(`
      INSERT IGNORE INTO students (s_no, s_name, password, email, class, enrollment_year) 
      VALUES 
        ('24215250202', '张三', ?, 'zhangsan@student.edu', '24网工专升本4班', 2024),
        ('24215250203', '王五', ?, 'wangwu@student.edu', '24网工专升本4班', 2024),
        ('22215220414', '李四', ?, 'lisi@student.edu', '22网络4班', 2022),
        ('22215220415', '赵六', ?, 'zhaoliu@student.edu', '22网络4班', 2022)
    `, [encryptedPassword, encryptedPassword, encryptedPassword, encryptedPassword]);

    // 4. 获取教师和课程ID
    const [teachers] = await connection.query('SELECT id FROM teachers');
    const [courses] = await connection.query('SELECT id FROM courses');
    const [students] = await connection.query('SELECT id, s_no FROM students');

    // 5. 插入教学班
    let classes = [];
    if (teachers.length > 0 && courses.length > 0) {
      await connection.query(`
        INSERT IGNORE INTO classes 
          (id, name, status, semester,teacher_id, course_id) 
        VALUES 
          (UUID(), '24网工专升本4班', 'active', '2024-2025-2', ?, ?),
          (UUID(), '22网络4班', 'active', '2023-2024-1', ?, ?),
          (UUID(), '24机器学习实验班', 'active', '2024-2025-3', ?, ?)
      `, [
        teachers[0].id, courses[0].id,
        teachers[1].id, courses[1].id,
        teachers[2].id, courses[2].id
      ]);

      // 获取班级ID
      [classes] = await connection.query('SELECT id, name FROM classes');
    }

    // 6. 关联学生与班级
    for (const cls of classes) {
      const classStudents = students.filter(s =>
        cls.name === '24网工专升本4班' && ['24215250202', '24215250203'].includes(s.s_no) ||
        cls.name === '22网络4班' && ['22215220414', '22215220415'].includes(s.s_no) ||
        cls.name === '24机器学习实验班' && ['24215250202', '22215220415'].includes(s.s_no)
      );

      for (const student of classStudents) {
        await connection.query(`
          INSERT IGNORE INTO class_students (class_id, student_id)
          VALUES (?, ?)
        `, [cls.id, student.id]);
      }
    }

    // 7. 插入实验数据
    const experimentsData = [
      {
        title: "数据表示与运算实验",
        short_description: "本实验聚焦计算机底层数据表示原理...",
        full_description: "本实验聚焦计算机底层数据表示原理，涵盖整数补码表示、浮点数IEEE 754标准实现及位级运算。学生需完成进制转换、掩码操作、浮点精度分析等任务，通过编程实现二进制与十六进制转换工具，深入理解数据在内存中的存储格式与运算特性。",
        course_name: "计算机系统基础",
        duration: 3,
        image_url: "img/1.jpg",
        objectives: [
          "掌握整数补码表示法及其运算规则",
          "理解IEEE 754浮点数标准及其实现细节"
        ],
        resources: [
          {
            name: "实验说明书",
            type: "pdf",
            file_path: "../storage/files/3207_0001/实验说明书.pdf",
            is_public: true
          },
          {
            name: "实验材料",
            type: "code",
            file_path: "../storage/files/3207_0001/datalab-handout.tar",
            is_public: false
          },
          {
            name: "实验模板",
            type: "docx",
            file_path: "../storage/files/3207_0001/学号_姓名_实验报告_1.docx",
            is_public: false
          }
        ]
      },
      {
        title: "BinaryBomb实验",
        short_description: "通过反汇编和GDB调试...",
        full_description: "通过反汇编和GDB调试技术，逐步拆解二进制炸弹程序，理解程序结构和控制流程。",
        course_name: "计算机系统基础",
        duration: 5,
        image_url: "img/2.jpg",
        objectives: [
          "掌握反汇编工具的使用",
          "理解程序控制流和函数调用",
          "学会使用GDB进行动态调试"
        ],
        resources: [
          {
            name: "BinaryBomb实验手册.pdf",
            type: "pdf",
            file_path: "cs/binary-bomb/manual.pdf",
            is_public: true
          },
          {
            name: "bomb.tar.gz",
            type: "code",
            file_path: "cs/binary-bomb/code.tar.gz",
            is_public: false
          }
        ]
      },
      {
        title: "缓冲区溢出攻击实验",
        short_description: "学习缓冲区溢出漏洞原理和利用技术...",
        full_description: "通过实际操作理解缓冲区溢出漏洞的产生原理，学习现代防护机制(如栈保护、ASLR)的工作原理，并实践基础的漏洞利用技术。",
        course_name: "计算机系统基础",
        duration: 6,
        image_url: "img/3.jpg",
        objectives: [
          "理解缓冲区溢出漏洞原理",
          "掌握基础shellcode编写技术",
          "学习现代防护机制的绕过方法"
        ],
        resources: [
          {
            name: "AttackLab实验指南.pdf",
            type: "pdf",
            file_path: "cs/buffer-overflow/manual.pdf",
            is_public: true
          },
          {
            name: "target.tar",
            type: "code",
            file_path: "cs/buffer-overflow/code.tar",
            is_public: false
          }
        ]
      },
      {
        title: "链接与装载实验",
        short_description: "深入理解ELF文件格式和链接过程...",
        full_description: "通过分析ELF文件结构，理解静态链接和动态链接的过程差异，实践符号解析、重定位等核心链接过程。",
        course_name: "计算机系统基础",
        duration: 4,
        image_url: "img/4.jpg",
        objectives: [
          "掌握ELF文件格式结构",
          "理解静态链接与动态链接差异",
          "实践符号解析与重定位过程"
        ],
        resources: [
          {
            name: "链接实验手册.pdf",
            type: "pdf",
            file_path: "cs/linking/manual.pdf",
            is_public: true
          },
          {
            name: "linklab-handout.tar",
            type: "code",
            file_path: "cs/linking/code.tar",
            is_public: false
          }
        ]
      }
    ];

    // 插入实验数据
    for (const exp of experimentsData) {
      const [expResult] = await connection.query(`
        INSERT INTO experiments 
          (title, short_description, full_description, course_name, duration, image_url) 
        VALUES (?, ?, ?, ?, ?, ?)
      `, [
        exp.title,
        exp.short_description,
        exp.full_description,
        exp.course_name,
        exp.duration,
        exp.image_url
      ]);

      const expId = expResult.insertId;

      // 插入实验目标
      for (const objective of exp.objectives) {
        await connection.query(`
          INSERT INTO experiment_objectives (experiment_id, objective)
          VALUES (?, ?)
        `, [expId, objective]);
      }

      // 插入实验资源
      for (const resource of exp.resources) {
        await connection.query(`
          INSERT INTO experiment_resources 
            (experiment_id, name, type, file_path, is_public) 
          VALUES (?, ?, ?, ?, ?)
        `, [
          expId,
          resource.name,
          resource.type,
          resource.file_path,
          resource.is_public
        ]);
      }
    }

    // 8. 更新实验评分标准
    const [allExperiments] = await connection.query('SELECT id FROM experiments');
    for (const exp of allExperiments) {
      await connection.query(`
        INSERT INTO experiment_criteria (experiment_id, name, proportion) 
        VALUES 
          (?, '实验报告', 50.0),
          (?, '代码实现', 40.0),
          (?, '测试结果', 10.0)
      `, [exp.id, exp.id, exp.id]);
    }

    // 9. 更新实验班级分配
    const classExperimentMap = {
      '24网工专升本4班': ['数据表示与运算实验', '缓冲区溢出攻击实验'],
      '22网络4班': ['BinaryBomb实验', '链接与装载实验'],
      '24机器学习实验班': ['数据表示与运算实验', '链接与装载实验']
    };

    for (const cls of classes) {
      const expTitles = classExperimentMap[cls.name] || [];

      for (const title of expTitles) {
        const [exp] = await connection.query(
          'SELECT id FROM experiments WHERE title = ?',
          [title]
        );

        if (exp.length > 0) {
          // 设置不同的截止日期
          const deadline = new Date();
          deadline.setDate(deadline.getDate() + 30);

          await connection.query(`
            INSERT INTO experiment_classes (experiment_id, class_id, deadline)
            VALUES (?, ?, ?)
          `, [exp[0].id, cls.id, deadline]);
        }
      }
    }

    // 在seedInitialData函数末尾添加
    // 10. 插入成绩数据
    const scoreData = [
      { s_no: '24215250202', experiment_id: 1, scores: [85, 90, 88] },
      { s_no: '24215250202', experiment_id: 2, scores: [92, 88, 90] },
      { s_no: '24215250203', experiment_id: 1, scores: [78, 85, 92] },
      { s_no: '24215250203', experiment_id: 3, scores: [80, 75, 85] },
      { s_no: '22215220414', experiment_id: 1, scores: [92, 88, 90] },
      { s_no: '22215220414', experiment_id: 4, scores: [85, 90, 88] },
      { s_no: '22215220415', experiment_id: 2, scores: [78, 85, 92] },
      { s_no: '22215220415', experiment_id: 4, scores: [80, 75, 85] }
    ];

    for (const score of scoreData) {
      const [student] = await connection.query('SELECT id FROM students WHERE s_no = ?', [score.s_no]);
      const [criteria] = await connection.query('SELECT id FROM experiment_criteria WHERE experiment_id = ?', [score.experiment_id]);

      if (student.length > 0 && criteria.length > 0) {
        // 插入每个评分标准的成绩
        for (let i = 0; i < criteria.length; i++) {
          await connection.query(`
        INSERT INTO experiment_scores 
          (experiment_id, student_id, criteria_id, score, status) 
        VALUES (?, ?, ?, ?, '已批改')
      `, [score.experiment_id, student[0].id, criteria[i].id, score.scores[i]]);
        }
      }
    }

    console.log('Initial data seeded successfully.');
  } catch (error) {
    console.error('Error seeding initial data:', error);
  }
}

initializeDatabase();